import pymysql
from twisted.enterprise import adbapi


class ChapterPipeline(object):

    def __init__(self,pool):
        """
        初始化类
        :param pool: 数据库连接池
        """
        self.dbpool = pool

    @classmethod
    def from_settings(self,settings):
        """
        数据库建立连接
        :param settings: 配置参数
        :return: 实例化参数
        """
        adbparams = dict(
            host=settings['MYSQL_HOST'],
            port=settings['MYSQL_PORT'],
            db=settings['MYSQL_DBNAME'],
            user=settings['MYSQL_USER'],
            password=settings['MYSQL_PASSWORD'],
            cp_reconnect=True,
            cursorclass=pymysql.cursors.DictCursor  # 指定cursor类型
        )
        # 连接数据池ConnectionPool，使用pymysql或者Mysqldb连接
        dbpool = adbapi.ConnectionPool('pymysql', **adbparams)
        return self(dbpool)

    def process_item(self, item, spider):
        # print(item)
        query = self.dbpool.runInteraction(self.do_insert, item)  # 指定操作方法和操作数据
        # 添加异常处理
        query.addCallback(self.handle_error)  # 处理异常
        return item

    def do_insert(self, cursor, item):
        id = item['href'].replace('/','')
        base_sql_data = (id, item['base'], item['title'], item['href'], item['page'], item['num'], '')

        child_data = item['chapter_list']
        child_sql_data = []
        for data in child_data:
            sort = data['href'].split('.')[0].split('/')[-1]
            child_sql_data.append((id,data['name'],'',data['href'],sort))

        # 对数据库进行插入操作，并不需要commit，twisted会自动commit
        insert_base_sql = """
           INSERT INTO xn_book(id,base,title,href,page,num,autor) 
           VALUES(%s,%s,%s,%s,%s,%s,%s);
           """
        cursor.execute(insert_base_sql, base_sql_data)

        insert_child_sql = '''
        INSERT INTO xn_chapter(p_id,name,text,uri,sort)
        VALUES(%s,%s,%s,%s,%s);
        '''
        cursor.executemany(insert_child_sql,child_sql_data)

    def handle_error(self, failure):
        if failure:
            # 打印错误信息
            print(failure)